Storage optimization for VARRAY columns

ABSTRACT

Methods and apparatus for efficiently storing data in a variable array (VARRAY) of a database system are disclosed. A method for storing data in a VARRAY having an associated large object segment in a system having a memory and a kernel includes determining a size of data to be inserted into the VARRAY. The method also includes determining when the size of the data is less than a threshold value, and storing the data as inline raw data in a column of the VARRAY that is present in the memory when the size of the data is less than the threshold value. When the size of the data is not less than the threshold value, the data is stored in a large object that is associated with the VARRAY. Further, when a VARRAY type is evolved to have a larger maximum size, existing data is not migrated or transformed.

CROSS REFERENCE TO RELATED PATENT APPLICATION

This patent application claims priority of U.S. Provisional PatentApplication No. 60/569,730, filed May 10, 2004, which is incorporatedherein by reference in its entirety.

BACKGROUND OF THE INVENTION

1. Field of Invention

The present invention relates to database systems. More specifically,the invention relates to enabling data in variable arrays (VARRAYs) tobe efficiently stored, particularly when a size of a VARRAY column isnot specified.

2. Description of the Related Art

The amount of memory space available in computing systems is typicallylimited, so it is crucial to efficiently allocate the memory space forusage. As such, the determination of where and how to store data withina computing system may be based upon how much data is to be stored. Forexample, in some instances, a relatively small amount of data may bestored as inline data in a column of a table, whereas a relatively largeamount of data may be stored as an object that is referenced by thetable.

A variable array or VARRAY is a variable-length ordered list of valuesof one type that may be specified by a user and created in a database,as for example a database available from Oracle, Inc. of Redwood Shores,Calif. That is, a VARRAY is an array, or an ordered set of data elementsof the same type, of variable size. Each element in a VARRAY generallyhas an index, which is a number that corresponds to the position of theelement in the VARRAY. The number of elements in a VARRAY is typicallyconsidered to be the size of the VARRAY. Typically, when a VARRAY typeis declared, a maximum size for the VARRAY, or an array limit, isspecified.

When creating a VARRAY, or a table of VARRAY columns, if there is nostorage clause for the VARRAY, i.e., if a user declines to specify anarray limit, a VARRAY column is created based on a maximum possible sizefor the VARRAY column. The maximum possible size is generally based onthe element size of elements to be stored in the VARRAY, a maximumnumber of elements that may potentially be stored in the VARRAY, and anecessary amount of system control information.

FIG. 1 is a process flow diagram which illustrates one method ofcreating a VARRAY, or a table of VARRAY columns, and storing dataassociated with the VARRAY. A method 10 of creating and using a table ofVARRAY columns begins at step 14 in which instructions to create aVARRAY type, VARRAY, are received. Once the instructions are received,it is determined whether the array limit for the VARRAY type, or amaximum number of elements which may be contained in a VARRAY, isspecified in step 18.

If the determination in step 18 that the size of a VARRAY column isspecified, then a VARRAY column is determined to be created based on thespecified size in step 22. Alternatively, if it is determined in step 18that the size of the VARRAY column is not specified, i.e., that there isno storage clause for the table of VARRAY columns, then a VARRAY columnis determined to be created in step 26 based on a maximum possible arraysize. Typically, the maximum possible array size may be determined by amaximum number of elements multiplied by the maximum size of an element,plus bytes needed to store system control information.

From steps 22 and 26, process flow moves to step 30 in which it isdetermined if the maximum size of a VARRAY column is less thanapproximately 4K bytes. As will be appreciated by those skilled in theart, the maximum size of data that may generally be stored inline perrow for a column is approximately 4K bytes. If it is determined that themaximum size of a column is less than approximately 4K bytes, data isstored in the VARRAY column as inline raw data in step 38, and themethod of creating a table of VARRAY columns and storing data in theVARRAY columns is completed. Alternatively, if it is determined that themaximum size of a VARRAY column is greater than or approximately equalto 4K bytes, then data is effectively stored in the VARRAY column as akernel internal large object (LOB), and the method of creating a tableof VARRAY columns and storing data in the VARRAY columns is completed.

When an array limit, or a maximum number of elements, for a VARRAY typeis not specified when the VARRAY type is created, a LOB is generallyallocated for data storage if the maximum possible size of each VARRAYcolumn is greater than approximately 4K bytes. With reference to FIG. 2,a LOB which is allocated for storage for a VARRAY column will bedescribed. A VARRAY 200 that either has a maximum possible size ofapproximately 4K bytes or more when no array limit is specified, or hasa specified array limit of approximately 4K bytes or more, is arrangedto have data stored in an object space 204 as a LOB 208. When a LOB 208is allocated for the storage of VARRAY data 216, LOB header informationor overhead 212 adds at least another several bytes, as forapproximately thirty bytes, to the size of LOB 208.

When LOB 208 is allocated because VARRAY 200 has a maximum possible sizeof approximately 4K bytes or more, and no array limit is specified,VARRAY data 216 stored in LOB 208 may be much less than approximately 4Kbytes. That is, the size of actual VARRAY data 216 may be suitable forstorage as a LOB locater with inline data, even though VARRAY data 216is stored in LOB 208 because LOB 208 has been allocated. When VARRAYdata 216 is significantly less than approximately 4K bytes, as forexample less than approximately thirty bytes, but stored in LOB 208, theextra bytes associated with header overhead 212 may representsignificant storage overhead. When header overhead 212 representsrelatively significant storage overhead, performance issues may ariseduring data write and data read operations. Such performance issues mayadversely affect the efficiency with which data write and data readoperations may be performed.

Storing data in a LOB segment, i.e., out of line, often has significantperformance impacts due to additional I/O operations which are used tofetch the stored data. In addition, storage in a LOB segment istypically allocated in discrete chunks, e.g., in some multiple of adatabase block size. Hence, even if an amount of data to be stored issignificantly less than the size of the discrete chuck into which thedata is to be stored, the entire chunk would be allocated, which resultsin significant overhead.

VARRAY types generally have an evolution feature which enables a user toalter, e.g., increase, the size of a VARRAY type. The size of a VARRAYtype may be increased in response to an increase in the size of anelement type stored in VARRAY columns, or generally in response to anincrease in a limit size of a VARRAY type. When the limit size of aVARRAY type is increased and results in the maximum size of a VARRAYcolumn increasing to approximately 4K bytes or greater, then VARRAY datathat is previously stored in the VARRAY columns generally is no longerstored as inline raw data, and is instead stored in a LOB. a LOB

FIG. 3 is a diagrammatic representation of how VARRAY data is storedbefore and after a limit size of a VARRAY column is increased from lessthan approximately 4K bytes in size to at least approximately 4K bytesin size. A VARRAY column 300 that is less than approximately 4K bytes insize is arranged to have its data stored inline in a table space 330.When a limit size of VARRAY column 300 is increased, as for example toapproximately 4K bytes or greater in size, VARRAY column 300′ which isapproximately 4K bytes or greater results. In addition, a LOB 346 in anobject space 342 is created to store data associated with VARRAY column300′.

Existing VARRAY data, which is stored in table space 330′ as inline rawdata, is effectively moved into LOB 346. In general, each row ofexisting VARRAY data of a column is stored in a separate LOB 346. A LOBlocator 338 is then stored into table space 330′ to identify LOB 346.ALOB

The need to essentially modify a VARRAY image to be stored in LOB 346,and to insert LOB locator 338 into table space 330′ is ofteninefficient, as the process of storing previously inlined raw data intoLOB 346, and inserting LOB locator 338 into table space 330′ isrelatively slow. When there are a relatively large number of rowsassociated with VARRAY column 300′, the process of storing VARRAY datathat was previously inlined as raw data in table space 330 may beparticularly time consuming. Further, evolving a VARRAY type typicallyrequires that existing data be modified, which is also time consumingand may utilize a significant amount of overhead.

Therefore, what is needed is a method and an apparatus which allowsVARRAY types to be efficiently created, particularly when array limitsare not specified, and also allows for less time-consuming evolutionfeatures associated with the VARRAY types. That is, what is needed is asystem which substantially minimizes the occurrences of storingrelatively small amounts of data in kernel internal LOBs, and alsosignificantly reduces the need to effectively copy inline raw data intokernel internal LOBs when a VARRAY limit size is increased. typicallytime consuming operations relating to data stored in VARRAY columns

SUMMARY OF THE INVENTION

The present invention relates to efficiently storing data in a variablearray (VARRAY) used in a database system. According to one aspect of thepresent invention, a method for storing data in a variable array havingan associated large object segment in a computing system having a memoryand a kernel includes determining a size of data to be inserted into thevariable array. The method also includes determining when the size ofthe data is less than a threshold value, and storing the data as inlineraw data in a column of the variable array that is present in the memorywhen it is determined that the size of the data is less than thethreshold value. When it is determined that the size of the data is notless than the threshold value, the data is stored in a large object thatis associated with the variable array. In one embodiment, the thresholdis approximately equal to an approximately maximum number of bytes whichmay possibly be stored in a row of the column.

When a VARRAY that is created for use within a database system is notspecified with an upper array limit, the VARRAY is typically createdusing a maximum possible size for the VARRAY. When this maximum possiblesize is at least as large as a threshold value, e.g., a maximum numberof bytes that may be stored in a row of a column of the VARRAY, andVARRAY data that is to be inserted or updated in a VARRAY column is atleast as large as the threshold value, the VARRAY data is storedout-of-line in a kernel internal large object while an large objectlocator is stored in the VARRAY column. Alternatively, when the size ofthe VARRAY data that is to be inserted or updated in the VARRAY columndoes not exceed the threshold value, the VARRAY data is stored as a LOBlocator with inline data in the VARRAY column. By enabling a location atwhich VARRAY data is stored to essentially be determined based on theactual size of the VARRAY data, even when a VARRAY is using an arraylimit that is the maximum possible size for the VARRAY, the storage ofVARRAY data in VARRAY columns is effectively optimized.

According to another aspect of the present invention, a variable arraydata structure includes a first segment and a first column. The firstsegment indicates that the data structure is arranged to include atleast one column which is associated with a first set of data that hasat least a threshold number of byte. The first column contains a secondset of data which includes less than the threshold number of bytes andis inlined in the first column.

According to still another aspect of the present invention, a method forstoring VARRAY data, which includes an associated large object segment,in a VARRAY associated with a computing system includes determining asize of the VARRAY data, determining when the size of the VARRAY data isless than a threshold value, and inlining the VARRAY data in a firstcolumn of the VARRAY that is present in the table space when it isdetermined that the size of the VARRAY data is less than the thresholdvalue. When it is determined that the size of the VARRAY data is notless than the threshold value, the method also includes storing theVARRAY data out-of-line in a large object internal to a kernel as wellas storing a large object locator in the VARRAY that identifies thelarge object and is stored in a second column of the VARRAY.

In accordance with one other aspect of the present invention, a methodfor evolving a VARRAY created using an array limit that is less than athreshold array limit involves increasing a size of the VARRAY from lessthan the threshold array limit to at least the threshold array limit,and storing a flag that is arranged to indicate that a column of theVARRAY in table space may include both inline VARRAY data andout-of-line VARRAY data that is stored in an associated large object. Inone embodiment, the method also includes determining a size of VARRAYdata to be stored in the column, determining when the size of the VARRAYdata is less than a threshold value, inlining the string of VARRAY datain the column of the VARRAY when it is determined that the size of thestring of VARRAY data is less than the threshold value, and storing thestring of VARRAY data out-of-line in a large object internal to a kernelwhen it is determined that the size of the string of VARRAY data is notless than the threshold value.

Other features and advantages of the invention will become readilyavailable apparent upon review of the following description inassociation with the accompanying drawings, where the same or similarstructures are designated with the same reference numerals.

BRIEF DESCRIPTION OF THE DRAWINGS

The invention may best be understood by reference to the followingdescription taken in conjunction with the accompanying drawings inwhich:

FIG. 1 is a process flow diagram which illustrates one method ofcreating a table of VARRAY columns and storing data associated with theVARRAY columns.

FIG. 2 is a diagrammatic representation of a kernel internal largeobject which is allocated for storage for of a VARRAY column.

FIG. 3 is a diagrammatic representation of how VARRAY data is storedbefore and after a limit size of a VARRAY column is increased from lessthan approximately 4K bytes in size to at least approximately 4K bytesin size.

FIG. 4 a is a diagrammatic representation of how data that is less thana threshold amount, e.g., less than approximately 4K bytes in size, isstored when a LOB segment has been allocated in accordance with anembodiment of the present invention.

FIG. 4 b is a diagrammatic representation of the storage of VARRAY datathat is approximately 4K bytes or more in size in accordance with anembodiment of the present invention.

FIG. 5 is a process flow diagram which illustrates the steps associatedwith one method of storing data in a VARRAY column of a VARRAY for whichno storage clause is specified and for which a maximum size of theVARRAY is greater than or equal to a threshold in accordance with anembodiment of the present invention.

FIG. 6 a is a diagrammatic representation of a VARRAY and acorresponding table space before and after the size of the VARRAY isincreased from less than a threshold to a size that is at least the sizeof a the threshold in accordance with an embodiment of the presentinvention.

FIG. 6 b is a diagrammatic representation of the storage of data whichexceeds a threshold amount when a VARRAY has undergone a type evolutionwhich results in an increased size in accordance with an embodiment ofthe present invention.

FIG. 7 is a block diagram of components that may be present in computersystems that implement embodiments of the present invention.

DETAILED DESCRIPTION OF THE EMBODIMENTS

In the description that follows, the present invention will be describedin reference to embodiments that test subsystems on a platform for asoftware application, such as a database application. However,embodiments of the invention are not limited to any particulararchitecture, environment, application, or implementation. For example,although embodiments will be described in reference to databaseapplications, the invention may be advantageously applied to anysoftware application. Therefore, the description of the embodiments thatfollows is for purposes of illustration and not limitation.

A variable array (VARRAY) type is generally used to represent avariable-length ordered set of elements. Since the size of the actualdata stored in a VARRAY is also variable, the amount of storage spaceneeded to store VARRAY data associated with the VARRAY may vary widely.When a VARRAY type is specified without an array limit, the VARRAY istypically created based on the maximum size associated with the VARRAYtype. When the maximum size is greater than or equal to approximately 4Kbytes, a kernel internal large object (LOB) is created to store dataassociated with a column of the VARRAY. When the actual size of dataassociated with the column of the VARRAY is significantly less than 4Kbytes, the use of a LOB to store the data is inefficient, and may causeperformance issues associated with the use of the VARRAY.

By substantially optimizing the storage of data in VARRAY columns,unnecessary overhead associated with LOBs may often be eliminated,better performance of an overall system may be achieved, and efficiencyof VARRAY related operations or features, e.g., VARRAY type evolution,may be improved. In one embodiment, when a VARRAY type is specifiedwithout an array limit and the maximum size of a column of the VARRAY issuch that a kernel internal LOB would be used to store data associatedwith a column, a LOB segment may be allocated in memory, or a tablespace. However, data that is to be stored in the column is not stored ina LOB unless the data has a size that is greater than or approximatelyequal to the maximum size of data that may generally be stored inline inthe table space. When the data has a size that is less than the maximumsize of data that may be stored inline, then the data is stored inlinein the table space associated with the VARRAY. Hence, by essentiallyavoiding substantially always storing VARRAY data into kernel internalLOBs if a VARRAY type is specified without an array limit and themaximum size of a column of the VARRAY is greater than the number ofbytes that may be stored inline in a row of a column, the efficiencywith which VARRAY data may be stored and retrieved is enhanced. That is,even though a LOB segment may be allocated for a VARRAY column, if theactual data associated with the VARRAY column is relatively small, or atleast smaller than an upper limit for the size of data that may bestored inline in a row of a table, the actual data may be stored as alocator with inline data, thereby effectively eliminating a need tocreate a LOB with LOB header information for the storage of the actualdata.

With reference to FIG. 4 a, the storage of data that is less than athreshold amount, e.g., less than approximately 4K bytes in size, when aLOB segment has been allocated will be described in accordance with anembodiment of the present invention. A VARRAY 404 that was not specifiedwith a storage clause and has a maximum size of approximately 4K bytesor more is typically allocated with a LOB segment in a table in a tablespace 408 associated with VARRAY 404. When VARRAY data associated withVARRAY 404 has a size that is approximately 4K bytes or more is to beinserted into a VARRAY column, the VARRAY data is stored as a kernelinternal LOB 416 in an object space 412. It should be appreciated thatwhen VARRAY data associated with a VARRAY column that is to be updatedis approximately 4K bytes or greater in size, the updated data is storedin LOB 416, while a LOB locator (not shown) may be written inline in theVARRAY column in table space 408 to identify LOB 416, as will bediscussed below with respect to FIG. 4 b.

When VARRAY data that is less than approximately 4K bytes in size iseffectively to be inserted into a VARRAY column of VARRAY 404, eventhough a LOB segment has been allocated, the VARRAY data is stored as aLOB locator with inline data into table space 408. That is, VARRAY datathat is less than approximately 4K bytes in size is not stored as a LOBin object space 412, but is instead stored into table space 408. Ingeneral, when VARRAY data that is to be updated with respect to a VARRAYcolumn is less than approximately 4K bytes in size, the VARRAY data isalso updated in table space 408.

The storage of VARRAY data that is less than approximately 4K bytes insize into table space 408, even though a LOB segment has been allocated,results in a more efficient write process, as well as a more efficientread process, when the VARRAY data is to be accessed. The increasedefficiency results, at least in part, from not having to access a LOB,and not having to write, or to read, any header overhead contained in aLOB. Since a LOB segment allocates data in chunks that are generallymultiples of a database block size, significant space overheads may beavoided with storing data that is less than approximately 4K bytes insize. Further, space in object space 412 may be allocated for other useswhen VARRAY data is inlined as raw data in table space 408 wheneverpossible.

If VARRAY data to be inserted into a column of a VARRAY, i.e., a VARRAYcolumn which was not created with a storage clause and has a maximumsize of 4K bytes or more, includes approximately 4K bytes or more, theVARRAY data is inserted into a LOB, while a LOB locator is written intotable space associated with the VARRAY column. FIG. 4 b is adiagrammatic representation of the storage of VARRAY data that isapproximately 4K bytes or more in size in accordance with an embodimentof the present invention. A VARRAY 424 which was created without astorage clause and has a maximum size of 4K bytes or more is generallyallocated with a LOB segment which is allocated in a table space 428. Asdiscussed above with respect to FIG. 4 a, when VARRAY data associatedwith a column of VARRAY 424 is to be inserted or updated and is lessthan approximately 4K bytes in size, the VARRAY data is stored in tablespace 428.

When VARRAY data to be inserted or updated in VARRAY 424 isapproximately 4K bytes or more in size, the VARRAY data is stored in aLOB 436 within an object space 432. A LOB 436, which include headeroverhead 438 that includes information associated with LOB 436, istypically allocated for each column associated with VARRAY 424 whichincludes approximately 4K bytes or more. A LOB locator 440, which isarranged to identify LOB 436, is then stored into table space 428 toenable LOB 436 to be identified with VARRAY data which corresponds to aVARRAY column of VARRAY 424. In one embodiment, LOB locator 440 may bewritten inline within space allocated to a VARRAY column.

Referring next to FIG. 5, the steps associated with one method ofstoring data in a VARRAY column of a VARRAY for which no storage clauseis specified and for which a maximum size of the VARRAY is greater thanor equal to a threshold will be described in accordance with anembodiment of the present invention. A process 500 of storing data in aVARRAY column begins at step 504 in which a VARRAY type is created, asfor example in response to a command to create a VARRAY associated witha database system, e.g., an Oracle RDBMS system available from Oracle,Inc. of Redwood Shores, Calif. The VARRAY type that is created allowsfor size limits and element types associated with the VARRAY type to bespecified. If a size limit is not specified, an array size limit for aVARRAY column of the VARRAY type is typically determined to be themaximum possible size of the VARRAY. In the described embodiment, anarray size limit or the amount of data that may be stored in a VARRAY isnot specified, and the maximum possible size of the VARRAY is greaterthan or equal to a threshold. The threshold may generally be widelyvaried. In the described embodiment, the threshold is approximately 4Kbytes, or approximately a maximum number of bytes which may be storedinline per row for a VARRAY column.

Once the VARRAY is created with a VARRAY column, a table segment inmemory, e.g., memory of a disk, or table space that includes the VARRAYcolumn is created in step 508. A LOB segment for the VARRAY type is thencreated in step 512, and stored in the memory, if the VARRAY created instep 504 is such that the VARRAY column has a maximum size that isgreater than or approximately equal to a threshold, e.g., a threshold ofapproximately 4K bytes.

A command to insert VARRAY data, or VARRAY values, into the tablesegment is received in step 516. The actual size of the VARRAY data tobe inserted into a VARRAY column associated with the VARRAY isdetermined in step 520. The determination of the actual size may involvedetermining how many elements are included in VARRAY data, anddetermining a size of each element. After the actual size of the VARRAYdata is determined in step 520, it is determined in step 524 whether theactual size of the VARRAY data is less than a threshold. The thresholdmay be approximately 4K bytes, or approximately a maximum number ofbytes which may be stored in line per row for a VARRAY column, asdiscussed above.

If it is determined that the actual size of the data is less than thethreshold, the VARRAY data is stored as a LOB locator with inline datain a VARRAY column in step 528, and the process of storing data in aVARRAY column is completed. Alternatively, if it is determined in step524 that the actual size of the data to be inserted is greater than orapproximately equal to the threshold, then a LOB locator is constructedin step 532. The LOB locator, which is used to identify a LOB that isarranged to contain the VARRAY data, may include any number of bytes,e.g., approximately thirty bytes. The LOB locator is then stored in step536 into the table space or memory, and the VARRAY data is storedout-of-line, or into a LOB in object space, in step 540. Once the VARRAYdata is stored out-of-line, the process of storing VARRAY data iscompleted.

Allowing a VARRAY to include VARRAY columns with data stored inline andVARRAY columns with data stored in a LOB, i.e., allowing a VARRAY inlineimage and LOB data to substantially coexist in VARRAY columns of aVARRAY, if there is no storage clause for the VARRAY and the maximumsize of the VARRAY is at least equal to a threshold enables VARRAY datastorage to be substantially optimized. The efficiency of the VARRAY datastorage is enhanced in that a relatively small amount of VARRAY data,e.g., data that is less than approximately thirty bytes in size, may bestored inline thereby avoiding the allocation of a LOB with LOB headerinformation for the storage of the relatively small amount of VARRAYdata. That is, LOB storage overhead is effectively eliminated for VARRAYdata that is smaller than a threshold size, as for example a thresholdsize of approximately 4K bytes. Effectively eliminating, orsignificantly reducing, the amount of overhead associated with thestorage of VARRAY data that is less than the threshold size willgenerally result in improved performance for the writing of and thereading of VARRAY data.

A VARRAY type evolution feature, which allows the maximum size of aVARRAY column associated with a VARRAY type to be increased, may be mademore efficient by substantially exploiting the ability for VARAY columnsof a VARRAY to support both inline VARRAY data and out-of-line VARRAYdata. When the size limit associated with a VARRAY type is increased,for example, from less than approximately 4K bytes to more thanapproximately 4K bytes, the existing inline VARRAY data will be allowedto remain unchanged. That is, the VARRAY data that is stored in a row ofa VARRAY column will not effectively be moved into a corresponding LOBand will, instead, remain as inline raw data. Since the cost ofaccessing all existing data during a type evolution, by allowingexisting VARRAY data to remain unchanged after an evolution form a sizeof less than approximately 4K bytes to a size of more than approximately4K bytes, the evolution may occur efficiently. Any new VARRAY data orupdated of existing VARRAY data may be stored as a LOB locator withinline data in table space. It should be appreciated that substantiallyonly unmodified existing data is stored as inline raw data.

With reference to FIGS. 6 a and 6 b, a VARRAY type evolution feature inwhich the size of a VARRAY type is increased from less than a thresholdamount to at least the threshold amount will be described in accordancewith an embodiment of the present invention. FIG. 6 a is a diagrammaticrepresentation of a VARRAY and a corresponding table space before andafter the size of the VARRAY is increased from less than a threshold toa size that is at least the size of a the threshold in accordance withan embodiment of the present invention. When data is to be stored in aVARRAY 604 with a maximum size of less than approximately 4K byteswhich, in the described embodiment, is a threshold size, the data isinserted or updated in table space 608, or a memory that is used tostore a table of VARRAY columns associated with VARRAY 604.

The format of inline raw data is distinguishable from a LOB locator withinline data. A LOB locator with inline data is distinguishable from aLOB locator with no inline data. Hence, existing inline raw data may bemaintained as is, while allowing for new data to be stored as a LOBlocator, either with or without inline data. Once a command is receivedto “evolve” VARRAY 604 from a size of less than approximately 4K bytesto a size of at least approximately 4K bytes, at least one flag 630, isstored into table space 608′ to indicate that VARRAY 604 may have acolumn with inline VARRAY data and with VARRAY data stored in a LOB 616within an object space 612. a LOBa LOB A check of whether flag 630 ispresent in table space 608′, and whether LOB locator 632 is present inVARRAY data stored in table space 608′, allows a suitable retrievaltechnique to be utilized to retrieve the VARRAY data.

When VARRAY data is less than approximately 4K bytes in size, the VARRAYdata is stored inline in a VARRAY column. As shown in FIG. 6 b, whenVARRAY data 670 that exceeds a threshold size is to be stored in acolumn of a VARRAY 654, i.e., a VARRAY column with a newly increasedsize that has been increased from a size that is less than a thresholdamount to a size that is at least equal to the threshold amount, VARRAYdata 670 is stored as a VARRAY image in a LOB 664 in object space 662. ALOB locator 682 which identifies LOB 664 is stored into an appropriaterow within table space 690 that is associated with the VARRAY column ofVARRAY 654.

For an UPDATE or INSERT statement or command after VARRAY 654 undergoesa size increase that results in the maximum size of columns of VARRAY654 being at least equal to a threshold size, if the new or updatedVARRAY data is smaller than the threshold size, which may beapproximately 4K bytes, the VARRAY data will be stored as a LOB locatorwith inline data within table space 658, unlike before an evolution,when it would be stored as inline raw data. Otherwise, if the new VARRAYdata is larger than the threshold size, VARRAY data will be stored as aLOB, e.g., VARRAY data 670 is stored in LOB 664. By allowing a typeevolution feature to be such that evolved VARRAY 654 may have columnswith both inline raw data and data stored as a LOB locator, a dataupgrade may occur without a significant performance penalty. In otherwords, the speed with which an overall system may operate is notsignificantly compromised even if there are a relatively large number ofrows associated with columns of VARRAY 654 before the size increase,since it is not necessary to effectively form out-of-line images of allVARRAY data that has been stored inline.

FIG. 7 shows a block diagram of components that may be present incomputer systems that implement embodiments of the invention. A computersystem 101 includes a processor 103 that executes instructions orcommands from computer programs, including operating systems. Althoughprocessors typically have memory caches also, processor 103 utilizesmemory 105, which may store instructions or computer code and data.

A fixed storage 107 may store computer programs and data such that it istypically persistent and provides more storage when compared to memory105. At present, a common fixed storage is one or more hard drives. Aremovable storage 109 provides mobility to computer programs and/or datathat are stored thereon. Examples of removable storage are floppy disks,tape, CD/ROM, flash memory devices, and the like.

Memory 103, fixed storage 107 and removable storage 109 provide examplesof computer readable storage media that may be utilized to store andretrieve computer programs incorporating computer codes that implementthe invention, data for use with the invention, and the like.Additionally, a data signal embodied in a carrier wave, e.g., in anetwork including the Internet, may be the computer readable storagemedium. An input 111 allows a user to interface with the system. Inputmay be done through the use of a keyboard, a mouse, buttons, dials, orany other input mechanism. An output 113 allows the system to provideoutput to the user. Output may be provided through a monitor, displayscreen, LEDs, printer or any other output mechanism.

A network interface 115 allows the system to interface with a network towhich it is connected. The system bus architecture of computer system101 is represented by arrows 117. The components shown in FIG. 7 may befound in many network devices and computer systems. However, componentsmay be added, deleted and combined. For example, fixed storage could bean array of hard drives or the fixed storage could be a file server thatis accessed through a network connection. Thus, FIG. 7 is forillustration purposes and not limitation.

Although only a few embodiments of the present invention have beendescribed, it should be understood that the present invention may beembodied in many other specific forms without departing from the spiritor the scope of the present invention. By way of example, while a valueof approximately 4K bytes has generally been described as being athreshold below which VARRAY data is stored as inline raw data and abovewhich VARRAY data is stored as an out-of-line image, the threshold valuemay vary widely. The 4K bytes generally represents a substantiallymaximum number of bytes which may be stored in a row of a column. Hence,the threshold value may generally be a maximum number of bytes which maybe stored in a row of a column. Alternatively, the threshold value maybe substantially any number of bytes as determined by an administratorof a system which uses VARRAYs.

While data associated with a VARRAY column has been described as beingstored in a kernel internal LOB when the data has a size that is atleast as large as a threshold, e.g., greater than or equal toapproximately the maximum mount that may be stored inline in a row of acolumn, the data may generally be stored in substantially any suitableobject. In addition, while an object in which data is stored isgenerally a kernel internal object, the object may be external to akernel in some instances.

In general, the steps associated with the methods of the presentinvention may vary widely. Steps may be added, removed, altered, andreordered without departing from the spirit or the scope of the presentinvention. For instance, the steps associated with creating a VARRAYwhen no size limit is specified and a maximum possible size is greaterthan a threshold value may vary. As an example, the step of receiving acommand to insert data or VARRAY values in a VARRAY may instead by astep of receiving a command to update data or VARRAY values associatedwith a VARRAY. Therefore, the present examples are to be considered asillustrative and not restrictive, and the invention is not to be limitedto the details given herein, but may be modified within the scope of theappended claims.

1. A method for storing data in a variable array associated with acomputing system, the variable array having an associated large objectsegment, the computing system having a memory and a kernel, the methodcomprising: determining a size of data, the data being associated withthe variable array; determining when the size of the data is less than athreshold value; storing the data as inline raw data in a column of thevariable array that is present in the memory when it is determined thatthe size of the data is less than the threshold value; and storing thedata in an external large object when it is determined that the size ofthe data is not less than the threshold value, wherein the large objectis associated with the variable array.
 2. The method of claim 1 furtherincluding: creating the variable array, wherein creating the variablearray includes creating the variable array to have an array limit thatis substantially a maximum possible size for the variable array andcreating the large object segment, the maximum possible size beinggreater than or approximately equal to the threshold value.
 3. Themethod of claim 2 wherein the maximum possible size for the variablearray is determined approximately based on a substantially maximumnumber of elements which may be stored in the variable array, asubstantially maximum size of each of the elements which may be storedin the variable array, and an amount of system control information. 4.The method of claim 1 wherein storing the data in the external largeobject includes: creating the external large object; and storing alocator which identifies a location of the external large object, thelocator being stored in place of the data.
 5. The method of claim 1wherein the threshold is approximately equal to a maximum number ofbytes that may be stored in a row for the column.
 6. The method of claim1 wherein the large object is an internal large object to the kernel. 7.The method of claim 1 further including: receiving the data as a part ofone of an update command and an insert command.
 8. The method of claim 1further including: increasing an array limit of the variable array froma first limit to a second limit, the first limit being less that thethreshold value and the second limit being greater than or approximatelyequal to the threshold value.
 9. The method of claim 8 furtherincluding: receiving the data as a part of one of an update command andan insert command.
 10. The method of claim 8 wherein any data stored inthe memory before the array limit is increased remains stored in thememory.
 11. A computer program product comprising: method for storingdata in a variable array associated with a computing system, thevariable array having an associated large object segment, the computingsystem having a memory and a kernel, the method comprising: code devicesthat cause a size of data to be determined, the data being associatedwith a variable array; code devices that cause a determination of whenthe size of the data is less than a threshold value; code devices thatcause the data to be stored as inline raw data in a column of thevariable array that is present in a memory when it is determined thatthe size of the data is less than the threshold value; code devices thatcause the data to be stored in a large object when it is determined thatthe size of the data is not less than the threshold value, wherein thelarge object is associated with the variable array and not included inthe memory; and a computer-readable medium that stores the code devices.12. The computer program product of claim 11 wherein the variable arrayhas an associated large object segment stored in the memory.
 13. Thecomputer program product of claim 11 further including: code devicesthat cause the variable array to be created, wherein the code devicesthat cause the variable array to be created include code devices thatcause the variable array to be created with an array limit that issubstantially a maximum possible size for the variable array and codedevices that cause the large object segment to be created, the maximumpossible size being greater than or approximately equal to the thresholdvalue.
 14. The computer program product of claim 11 wherein thethreshold is approximately equal to a maximum number of bytes that maybe stored in a row for the column.
 15. The computer program product ofclaim 11 further including: code devices that cause an array limit ofthe variable array to be increased from a first limit to a second limit,the first limit being less that the threshold value and the second limitbeing greater than or approximately equal to the threshold value; andcode devices that cause a flag to be stored in the memory to indicatethat the data is arranged to be stored as a LOB locator with inlineddata when it is determined that the size of the data is less than thethreshold value and that the data is arranged to be stored in the largeobject when it is determined that the size of the data is not less thanthe threshold value.
 16. A system for storing data in a variable array,the variable array having an associated large object segment, the systemcomprising: a memory; a kernel; means for determining a size of data,the data being associated with the variable array; means for determiningwhen the size of the data is less than a threshold value; means forstoring the data as inline raw data in a column of the variable arraythat is present in the memory when it is determined that the size of thedata is less than the threshold value; and means for storing the data ina large object when it is determined that the size of the data is notless than the threshold value, wherein the large object is associatedwith the variable array and present in the kernel.
 17. The system ofclaim 16 further including: means for creating the variable array,wherein the means for creating the variable array include means forcreating the variable array to have an array limit that is substantiallya maximum possible size for the variable array and means for creatingthe large object segment, the maximum possible size being greater thanor approximately equal to the threshold value.
 18. The system of claim17 wherein the maximum possible size for the variable array isdetermined approximately based on a substantially maximum number ofelements which may be stored in the variable array, a substantiallymaximum size of each of the elements which may be stored in the variablearray, and an amount of system control information.
 19. The system ofclaim 16 wherein the means for storing the data in the large objectinclude: means for creating the large object; and means for storing alocator which identifies a location of the large object, the locatorbeing stored in the memory.
 20. The system of claim 16 wherein thethreshold is approximately equal to a maximum number of bytes that maybe stored in a row for the column.
 21. The system of claim 16 furtherincluding: means for increasing an array limit of the variable arrayfrom a first limit to a second limit, the first limit being less thatthe threshold value and the second limit being greater than orapproximately equal to the threshold value; and means for storing a flagin the memory to indicate that the data is arranged to be stored asinline raw data when it is determined that the size of the data is lessthan the threshold value and that the data is arranged to be stored inthe large object when it is determined that the size of the data is notless than the threshold value.
 22. A variable array data structurecomprising: a first segment, the first segment being arranged toindicate that the data structure is arranged to include at least onecolumn which is associated with a first set of data, the first set ofdata including at least a threshold number of bytes; and a first column,the first column containing a second set of data, the second set of dataincluding less than the threshold number of bytes and being inlined inthe first column.
 23. The variable array data structure of claim 22wherein the first segment is a large object segment that is allocatedwhen the variable array data structure is created.
 24. A method forstoring VARRAY data in a VARRAY associated with a computing system, theVARRAY having an associated large object segment, the computing systemhaving a table space, the method comprising: determining a size of theVARRAY data; determining when the size of the VARRAY data is less than athreshold value; storing the VARRAY as large object segment locator withinline data in a first column of the VARRAY that is present in the tablespace when it is determined that the size of the VARRAY data is lessthan the threshold value; storing the VARRAY data out-of-line in a largeobject internal to a kernel when it is determined that the size of theVARRAY data is not less than the threshold value; and storing a largeobject locator in the VARRAY when it is determined that the size of theVARRAY data is not less than the threshold value, wherein the largeobject locator is arranged to identify the large object and is stored ina second column of the VARRAY.
 25. The method of claim 24 furtherincluding: creating the VARRAY in response to a create command, thecreate command being specified without an array limit, wherein creatingthe VARRAY includes creating the VARRAY to have an array limit that issubstantially a maximum possible size for the variable array andcreating the large object segment, the maximum possible size beinggreater than or approximately equal to the threshold value.
 26. Themethod of claim 25 wherein the threshold is approximately a maximumnumber of bytes that may be stored in a row of the first column.
 27. Amethod for evolving a VARRAY associated with a computing system, theVARRAY being created using an array limit that is less than a thresholdarray limit, the computing system having a table space, the methodcomprising: increasing a size of the VARRAY from less than the thresholdarray limit to at least the threshold array limit; and storing anindicator, the indicator being arranged to indicate that a column of theVARRAY in table space may include both inline VARRAY data andout-of-line VARRAY data that is stored in an associated large object.28. The method of claim 27 further including: determining a size ofVARRAY data to be stored in the column; determining when the size of theVARRAY data is less than a threshold value; inlining the VARRAY data inthe column of the VARRAY when it is determined that the size of theVARRAY data is less than the threshold value; storing the VARRAY dataout-of-line in a large object internal to a kernel when it is determinedthat the size of the VARRAY data is not less than the threshold value;and storing a large object locator in the column of the VARRAY when itis determined that the size of the VARRAY data is not less than thethreshold value, wherein the large object locator is arranged toidentify the large object.
 29. The method of claim 27 wherein thethreshold is approximately a maximum number of bytes that may be storedin a row of the first column.